--  MySQL有一个系统变量autocommit，用来自动提交事务。
SHOW VARIABLES LIKE 'autocommit';
--  默认是开启的，也就意味着在默认情况下，如果不显示地执行  START TRANSACTION 或者 BEGIN 开启一个事务，那么每条SQL语句都算是一个独立的事务，这种特性被称作事务的自动提交；
--   如果想关闭这种自动提交的功能，可以使用两种办法
--  1. 显式使用START TRANSACTION 或者 BEGIN 开启一个事务
--  这样在本次事务中提交或者回滚前会暂时关闭自动提交的u
--  2. 把系统变量 autocommit 设置为 OFF
SET AUTOCOMMIT = off;

/*========== 模拟转账 ==========*/
--  创建账号表
create table account(
     id int primary key auto_increment  COMMENT '主键',
     name varchar(20) COMMENT '姓名',
     money double COMMENT '余额'
);
--  初始化数据
insert into account values (1,'小明',50);
insert into account values (2,'小红',10);

/*==============事务回滚==================*/
begin;  -- 开启事务
/* Query OK, 0 rows affected (0.00 sec) */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    50 |
|  2 | 小红   |    10 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

update account set money=money-10 where id =1;
/*
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 */

update account set money=money+10 where id =2;
/*
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    40 |
|  2 | 小红   |    20 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

rollback;  -- 回滚
/* Query OK, 0 rows affected (0.01 sec) */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    50 |
|  2 | 小红   |    10 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

 /*==============事务提交==================*/
begin;
/* Query OK, 0 rows affected (0.00 sec) */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    50 |
|  2 | 小红   |    10 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

update account set money=money-10 where id=1;
/*
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
*/

update account set money=money+10 where id=2;
/*
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    40 |
|  2 | 小红   |    20 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

commit;  -- 提交事务
/* Query OK, 0 rows affected (0.00 sec) */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    40 |
|  2 | 小红   |    20 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

/*==============事务隐式提交==================*/
 /*
  隐式提交：
    - 数据库定义语言DDL，像create、alter、drop
    - 事务控制或关于锁定的语句，比如，前一个事务未提交，又开启了一个新的事务
    - 加载数据的语句，比如load data
    - 关于MySQL复制的一些语句 ：slave
  */
select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    50 |
|  2 | 小红   |    10 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

begin;
/*Query OK, 0 rows affected (0.00 sec)*/

update account set money=money+10 where id=2;
/*Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 */

begin;  -- 前一个事务未提交又开启了新的事务，则前一个事务隐式提交了
/*Query OK, 0 rows affected (0.01 sec)*/

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    50 |
|  2 | 小红   |    20 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

 /*==============保存点==================*/
/* 语法：
-- 定义保存点
savepoint  保存点名称;
-- 回滚到某个保存点，如果rollback后面不跟随保存点名称，则直接回滚到事务之前的状态
rollback [work] to [savepoint] 保存点名称 ;
-- 删除保存点
release savepoint 保存点名称;
 */
begin;
-- Query OK, 0 rows affected (0.00 sec)

update account set money=money-10 where id =1;
/*Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0*/

savepoint s1; -- 定义保存点
/*Query OK, 0 rows affected (0.01 sec)*/

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    40 |
|  2 | 小红   |    10 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */

update account set money=money+1 where id =2;
/*
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 */

rollback to s1; -- 回退到保存点
/* Query OK, 0 rows affected (0.00 sec) */

select * from account;
/*
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |    40 |
|  2 | 小红   |    10 |
+----+--------+-------+
2 rows in set (0.00 sec)
 */
